package com.itheima.mapper;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.itheima.pojo.Member;
import com.itheima.vo.RptDateVo;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import java.util.List;

public interface MemberMapper extends BaseMapper<Member> {
    @Select("SELECT r.fyear AS fyear,r.fmonth AS fmonth,COUNT(m.id) AS total\n" +
            "FROM rpt_date r \n" +
            "LEFT JOIN t_member m ON r.id = m.create_date_id\n" +
            "WHERE r.id BETWEEN DATE_FORMAT(NOW(),'%Y%m%d')-10000  AND DATE_FORMAT(NOW(),'%Y%m%d')\n" +
            "GROUP BY r.fyear,r.fmonth")
    public List<RptDateVo> findAmounByYear();


    //按照星期查询
    @Select("SELECT r.fmonth AS fmonth,r.fweek AS fweek,COUNT(m.id) AS total\n" +
            "FROM rpt_date r \n" +
            "LEFT JOIN t_member m ON r.id = m.create_date_id\n" +
            "WHERE r.id BETWEEN #{start} AND #{end}\n" +
            "GROUP BY r.fmonth,r.fweek")
    public List<RptDateVo> findDateByWeek(@Param("start") long start, @Param("end") long end );

    //按月查询
    @Select("SELECT r.fmonth AS fmonth,COUNT(m.id) AS total\n" +
            "FROM rpt_date r \n" +
            "LEFT JOIN t_member m ON r.id = m.create_date_id\n" +
            "WHERE r.id BETWEEN #{start} AND #{end}\n" +
            "GROUP BY r.fmonth")
    public List<RptDateVo> findDateByMonth(@Param("start") long start, @Param("end") long end );


    @Select("SELECT r.fyear AS fyear,r.fquarter AS fquarter,COUNT(m.id) AS total\n" +
            "FROM rpt_date r \n" +
            "LEFT JOIN t_member m ON r.id = m.create_date_id\n" +
            "WHERE r.id BETWEEN #{start} AND #{end}\n" +
            "GROUP BY r.fyear,r.fquarter")
    public  List<RptDateVo> findDateByQuarter(@Param("start") long start, @Param("end") long end );

    @Select("SELECT r.fyear AS fyear,COUNT(m.id) AS total\n" +
            "FROM rpt_date r \n" +
            "LEFT JOIN t_member m ON r.id = m.create_date_id\n" +
            "WHERE r.id BETWEEN #{start} AND #{end}\n" +
            "GROUP BY r.fyear")
    public  List<RptDateVo> findDateByYear(@Param("start") long start,@Param("end") long end );



}
